package bitch;

import java.sql.*;



public class Database
	{
	private Connection databaseConnection;
	private Statement statement;
	private Statement statement2;
	private Statement messageIncStatement;
	
	public Database()
		{
		//Initial setup
		try
			{Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();}
		catch (InstantiationException e)
			{e.printStackTrace();}
		catch (IllegalAccessException e)
			{e.printStackTrace();}
		catch (ClassNotFoundException e)
			{e.printStackTrace();}
		try
			{
			databaseConnection=DriverManager.getConnection("jdbc:derby:awesomedb;create=true");
			
			statement = databaseConnection.createStatement();
			statement2 = databaseConnection.createStatement();
			messageIncStatement = databaseConnection.createStatement();
			
			//Check if tables exist
			DatabaseMetaData dbmd = databaseConnection.getMetaData();
			
			ResultSet rs = dbmd.getTables(null,null,null,new String[] {"TABLE"});
			if(!rs.next())
				createDatabase();
			else
				System.out.println("Database exists.");
			
			}
		catch (SQLException e)
			{e.printStackTrace();}
		}
	public ResultSet query(String q)
		{
		try
			{return statement.executeQuery(q);}
		catch (SQLException e)
			{e.printStackTrace();}
		return null;
		}
	public ResultSet getRooms()
		{
		try
			{return statement2.executeQuery("SELECT * FROM Room");}
		catch (SQLException e)
			{e.printStackTrace();}
		return null;
		}
	public ResultSet getDepartments()
		{
		try
			{return statement2.executeQuery("SELECT * FROM Department");}
		catch (SQLException e)
			{e.printStackTrace();}
		return null;
		}
	public ResultSet getPersons()
		{
		try
			{return statement2.executeQuery("SELECT * FROM Person");}
		catch (SQLException e)
			{e.printStackTrace();}
		return null;
		}
	public ResultSet getMessages(int personID)
		{
		try
			{return statement2.executeQuery("SELECT * FROM Message WHERE ownerID="+personID+" AND isDeleted=0");}
		catch (SQLException e)
			{e.printStackTrace();}
		return null;
		}
	
	
	public ResultSet getParticipants(int ownerID,int appointmentID,boolean includeDenied)
		{
		String condition = "ownerID="+ownerID+" AND appointmentID="+appointmentID;
		if(!includeDenied)
			 condition+=" AND type<3";
		try
			{return statement2.executeQuery("SELECT participantID,type FROM participation WHERE "+condition+"");}
		catch (SQLException e)
			{e.printStackTrace();}
		return null;
		}
	//Quickly Update the database with the query. Returns the generated key, if any. 
	public int update(String q)
		{
		int key = -1;
		try
			{statement.executeUpdate(q,Statement.RETURN_GENERATED_KEYS);
			ResultSet keys = statement.getGeneratedKeys();
			if(keys!=null)
				{
				if (keys.next())
					key=keys.getInt(1+0);
				}
			}
		catch (SQLException e)
			{e.printStackTrace();}
		return key;
		}
	public int messageCounterUp(int personID)
		{
		int result=0;
		try
			{
			ResultSet rs = messageIncStatement.executeQuery("SELECT nextMessageID FROM Person WHERE personID="+personID+"");
			if (rs.next())
				result = rs.getInt(1+0);
			else
				System.err.println("Trying to send Message to nonexisting Person!");
			rs.close();
			}
		catch (SQLException e)
			{e.printStackTrace();}
		update("UPDATE Person SET nextMessageID="+String.valueOf(result+1)+" WHERE personID="+personID);
		return result;
		}

	public void dispose()
		{
		//Clean shutdown
		try
			{statement.close();
			statement2.close();
			messageIncStatement.close();
			databaseConnection.close();}
		catch (SQLException e1)
			{e1.printStackTrace();}
		try
			{DriverManager.getConnection("jdbc:derby:;shutdown=true");}
		catch (SQLException e)
			{System.out.println("Database shutdown.");}
		}
	
	public void createDatabase()
		{
		String str;
		str="CREATE TABLE Room(" +
			"roomID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1)," +
			"roomName VARCHAR(64) NOT NULL," +
			"size INT NOT NULL," +
			"PRIMARY KEY (roomID)" +
			")";
		update(str);
		str="CREATE TABLE Department(" +
			"departmentID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1)," +
			"departmentName VARCHAR(64) NOT NULL," +
			"PRIMARY KEY (departmentID)" +
			")";
		update(str);
		str="CREATE TABLE Person(" +
			"personID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1)," +
			"email VARCHAR(64) UNIQUE NOT NULL," +
			"password VARCHAR(64) NOT NULL," +
			"realName VARCHAR(64) NOT NULL," +
			"departmentID INT NOT NULL," +
			"nextMessageID INT NOT NULL," +
			"PRIMARY KEY (personID)" +
			")"; 
		update(str);
		str="CREATE TABLE Message(" +
			"ownerID INT NOT NULL," +
			"messageID INT NOT NULL," +
			"type INT NOT NULL," +
			"isDeleted INT NOT NULL," +	
			"sentTime TIMESTAMP NOT NULL," +
			"auxString LONG VARCHAR," +
			"personDeniedID INT," +
			"appointmentOwnerID INT," +
			"appointmentID INT," +
			"newAppointmentID INT," +
			"PRIMARY KEY (ownerID,messageID)" +
			")";
		update(str); 
		str="CREATE TABLE Appointment(" +
			"ownerID INT NOT NULL," +
			"appointmentID INT NOT NULL," +
			"startTime TIMESTAMP NOT NULL," +
			"endTime TIMESTAMP NOT NULL," +	
			"subject VARCHAR(64) NOT NULL," +
			"description LONG VARCHAR," +
			"isCancelled INT NOT NULL," +
			"locationName VARCHAR(64)," +
			"roomReservationID INT," +
			"changedToID INT," +
			"PRIMARY KEY (ownerID,appointmentID)" +
			")";
		update(str);
		str="CREATE TABLE participation(" +
			"participantID INT NOT NULL," +
			"ownerID INT NOT NULL," +
			"appointmentID INT NOT NULL," +
			"type INT NOT NULL," +
			"PRIMARY KEY (participantID,ownerID,appointmentID)" +
			")";
		update(str);
		System.out.println("Database tables created!");
		}
	public int getNextAppointmentID(int personID)
		{
		int result=0;
		try
			{
			ResultSet rs = statement.executeQuery("SELECT appointmentID FROM Appointment WHERE ownerID="+personID+"");
			
			while (rs.next())
				{
				int id = rs.getInt(1+0)+1;
				if(id>result)
					result = id;
				}
			rs.close();
			}
		catch (SQLException e)
			{e.printStackTrace();}
		return result;
		}
	}
